sql query ...

[paul_y3k]

i know there's a few dba's on the site, and once again I need some help.

 

i've inherited a sql table that is structured as

 

ref name value

------------------

1 first fred

1 second hello

1 third dog

1 fourth cat

 

so basically a set of values for a reference.

i'm trying to pull out a couple of those values, for a given reference, but my sql isn't up to much. i.e

 

ref value

1 fred,cat

 

select name where ref=1 and value='first' and value='third'

doesn't work, and I dont think I should be using or.

 

from reading my limited library I'm not sure it's acually possible ?

[ShamelessTT]

So am i right in thinking you want to select where ref = 1 but return a subset of the values from each row returned into one row?

[Snooze]

SELECT name, value FROM table WHERE ref='' AND (name='first' OR name='fourth');

[ShamelessTT]

He'll get more than one row with that query, doesnt he just want 1 row? i.e 1 fred,cat with a combination of the values from other rows

[paul_y3k]

yep thats right, i think.

I'm going to create a second table, lets call it test. which will be populated from this select. i.e

 

REF ... FIRST ... THIRD

1 Fred Dog

[Snooze]

Disclaimer: This looks quite inefficient, and there's probably a better way of doing it that avoids the join, but I'm no DBA!

 

CREATE TABLE test AS

SELECT table_one.ref, table_one.first, table_two.third

FROM table table_one, table table_two

WHERE table_one.ref = table_two.ref

AND table_one.name = 'first'

AND table_two.name = 'third';

 

(or something like that?!)

[grahamc]

SELECT name, value FROM table WHERE ref='' AND (name='first' OR name='fourth');

 

SELECT name, value

INTO dbo.tbl_xxx

FROM table WHERE ref='' AND (name='first' OR name='fourth');

 

for if you want to create a table from a select

[Snooze]

I thought SELECT INTO was a non-standard thing?

[grahamc]

I thought SELECT INTO was a non-standard thing?

 

its not a recommended option, as it is quite a S**T way of doing things, but it is easy. And microsoft based sql...

[paul_y3k]

SELECT name, value

INTO dbo.tbl_xxx

FROM table WHERE ref='' AND (name='first' OR name='fourth');

 

But that wouldn't work as it's using an OR in the select and i want both values ?

 

 

 

CREATE TABLE test AS

SELECT table_one.ref, table_one.first, table_two.third

FROM table table_one, table table_two

WHERE table_one.ref = table_two.ref

AND table_one.name = 'first'

AND table_two.name = 'third';

 

i think my second example has confused things. so lets forget about the second table stuff for the moment. All I want is the values from the table where name = first and name = third.

 

ie. 1 FRED DOG

 

but the and keyword doesn't work.

[Snooze]

Just drop the CREATE TABLE line from my stuff before. Does that give you what you're after?

 

Oh - if you want it for a specific reference, just add an AND clause like "AND table_one.ref = '' "

[Snooze]

....and microsoft based sql...

 

Ah.... I see.... cheers, Graham!

[grahamc]

you mean, you want to concatenate multiple fields into one field for a given referance number? if so, then its rather difficult, depending on hopw many column values you want to use.

 

1 | Cat, dog, mouse

2 | toyota, nissan, ford

3 | dish, bowl, plate

 

For example?

[paul_y3k]

you mean, you want to concatenate multiple fields into one field for a given referance number? if so, then its rather difficult, depending on hopw many column values you want to use.

 

1 | Cat, dog, mouse

2 | toyota, nissan, ford

3 | dish, bowl, plate

 

For example?

 

Dont think so. i know I'm not exapling this very well at all so thanks for bearing with me.

 

lets try again ...

 

the table is defined with the following columns :

 

REF A_data b_data

 

Each row consists of the job reference (REF), a work item (a_data) and it's associated value (b_data). We can have multiple items and values per job. So we see a table with the following contents

 

REF a_data b_data

1 ITEM1 widget

1 ITEM2 doodahs

1 ITEM3 sprockett

2 ITEM1 big widget

2 ITEM2 big doodah

2 ITEM3 big sprockett

3 ITEM1 small widget

etc etc

 

I need to select the b_data for ITEM1 and ITEM3 for a given reference.

i.e SELECT b_data where REF=1 and a_data=ITEM1 and a_data=ITEM3.

 

to get a returrn of

 

1 widget sprockett

 

However that piece of sql returns no rows, and using OR doesn't seem to help either. So my problem is in the where clause ? or is it due to a poor table structure ?

[Snooze]

Did you try this, Paul:

 

SELECT table_one.ref, table_one.b_data, table_two.b_data

FROM table table_one, table table_two

WHERE table_one.ref = table_two.ref

AND table_one.ref = '1'

AND table_one.a_data = 'ITEM1'

AND table_two.a_data = 'ITEM3';

 

?

[paul_y3k]

Did you try this, Paul:

 

SELECT table_one.ref, table_one.b_data, table_two.b_data

FROM table table_one, table table_two

WHERE table_one.ref = table_two.ref

AND table_one.ref = '1'

AND table_one.a_data = 'ITEM1'

AND table_two.a_data = 'ITEM3';

 

?

 

I'm going to ... but i dont understand where you are getting table two from ??

[chilli]

this gives you _exactly_ what you want as described.

 

select a.ref, a.col1, b.col2 from (select ref, b_data col1 from my_table where a_data="item1") a, (select ref, b_data col2 from my_table where a_data="item3") b where a.ref = b.ref = 1;

assuming the table in my case is called "my_table", substitute as required etc)

 

took a minute, only just seen this thread.

 

bit of a weird thing to want to do though, is the db design actually any good if you need to do this sort of query.

 

there are other ways to achieve this too, which way to go kinda depends on other factors but in this simple case this does what you asked (if I understood what you asked correctly!)

[MrRalphMan]

Dont think so. i know I'm not exapling this very well at all so thanks for bearing with me.

 

lets try again ...

 

the table is defined with the following columns :

 

REF A_data b_data

 

Each row consists of the job reference (REF), a work item (a_data) and it's associated value (b_data). We can have multiple items and values per job. So we see a table with the following contents

 

REF a_data b_data

1 ITEM1 widget

1 ITEM2 doodahs

1 ITEM3 sprockett

2 ITEM1 big widget

2 ITEM2 big doodah

2 ITEM3 big sprockett

3 ITEM1 small widget

etc etc

 

I need to select the b_data for ITEM1 and ITEM3 for a given reference.

i.e SELECT b_data where REF=1 and a_data=ITEM1 and a_data=ITEM3.

 

to get a returrn of

 

1 widget sprockett

 

However that piece of sql returns no rows, and using OR doesn't seem to help either. So my problem is in the where clause ? or is it due to a poor table structure ?

 

I'm seriously having an issue working out what you are trying to pull from the table..

What data do you want?

 

Not in SQL terms, but like..

 

Table has job entries, an item number etc..

 

I would like to pull all entries from the DB where the Work Item is the same as xxx...

 

 

You seem to want data from three columns for more then one row. Maybe it's the DB structure, this is where relational DB's come into their own.

 

More work to design and maintain, but easier to pull the data off them..

 

You'd have something like the following.. ID Fields are all autonumber.

 

Job Ref Table.

 

 

ID Field Job Ref

1 JB01

2 JB02

3 JB99

 

 

Item Table

 

ID Field JobID Work Item

 

1 1 ITEM 1

2 1 ITEM 2

3 2 ITEM 9

 

Then a Value table.

 

 

ID Field ITEMID Item Value

 

1 1 Widget

2 3 Small Widget.

 

So you'd get

 

JB01 Item 1 Widget...

 

 

I think I just confused myself also, but it does mean you can have many to one links..

 

IE you might have 20 items hanging off one Job ref...

 

Cheers,

 

Paul.

[grahamc]

it really does seem like you are trying to turn rows into columns, which in SQL is rather difficult. especially if you have a large list of ITEM NUMBERS. If you only have a couple of ITEM NUMBERS then see CHILLIs response, and just replicate fo each item. However this is not an option if you have a lot of them.

[chilli]

it really does seem like you are trying to turn rows into columns, which in SQL is rather difficult. especially if you have a large list of ITEM NUMBERS. If you only have a couple of ITEM NUMBERS then see CHILLIs response, and just replicate fo each item. However this is not an option if you have a lot of them.

 

that's the problem, if you want to turn this into a generic solution then you probably need to pivot the data - otherwise it's a square peg and a round hole. For what you asked though, the solution is simple enough - if you are working with legacy data and that's all you need to do I'd use that and move on to drinking beer. If it's new, I'd redesign the db, then move onto drinking beer

[grahamc]

that's the problem, if you want to turn this into a generic solution then you probably need to pivot the data - otherwise it's a square peg and a round hole. For what you asked though, the solution is simple enough - if you are working with legacy data and that's all you need to do I'd use that and move on to drinking beer. If it's new, I'd redesign the db, then move onto drinking beer

 

slightly wrong... start by drinking beer, continue with drinking beer and finish with drinking beer...

 

BTW - SQL 2005 does have a pivot function that is supposed to do this, but I have not had a chance to try it.

[chilli]

slightly wrong... start by drinking beer, continue with drinking beer and finish with drinking beer...

 

this is probably the best solution to this problem proposed so far

 

at least if you start by drinking beer the problem won't seem very important for long...

[grahamc]

this is probably the best solution to this problem proposed so far

 

at least if you start by drinking beer the problem won't seem very important for long...

 

its the only true solution!! works for all problems

[paul_y3k]

cheers guys, i'm glad it's not just me thats getting confused with this.

yep it's a legacy system so I can't change it. All I'm trying to do it get item1 and item3 for each job id. but yep the table doesn't really support it.

 

and yep I've taken the adivce and has several beers and a curry