selecting different field values in linked table

kemu_

i have two diff tables, and i need to do a search in them.

their sturcture is like this:

Table1
id, name

Table2
id, table1.id, name, value

So i need to get names from table1 where name in table2 could have two diff values. I dont know if im able to describe my problem clearly enough.

eg.
there would be following data in table2:

1,1,name1, value1
2,1,name2, value2
3,2,name3,value3
4,1,name4,value4

I would want to get those where table2 name would have both "name2" and "name3". And the values can be searchable aswell.

nemonoman

It sounds like you're trying to construct a simple JOIN of these two tables:

SELECT *
FROM table1, table2
WHERE table1.id=table2.table1.id
AND name='name1'

I suggest a quick google of "SQL join statements tutorial".

kemu_

Thats not excatly the problem. The problem is when u need it to have two (or more) rows in table2.

so a simple :
SELECT *
FROM table1, table2
WHERE table1.id=table2.table1.id
AND name='name1'

isnt enough

basically what i mean is that it would have to be like this:

SELECT *
FROM table1, table2
WHERE table1.id=table2.table1.id
AND name='name1' AND name='name2'

but that ofcourse doesnt work as the field name cant have values of "name2" and "name1" at the same time...

the db im using is postgresql

kemu_

Doesnt any1 understand my problem (i know my description lacks some details 😉 ), or is it just impossible to do?

bubblenut

Do you mean OR
select this from table1 and table2 where the value of the name column in table2 is either 'name2' OR 'name3'?
If this is the case then the query would be

SELECT * FROM table1, table2 WHERE table1.id=table2.table1.id AND (table2.name='value2' OR table2.name='value3')

Or possibly this syntax (this is MySQL syntax but I'm sure postgres has something similar

SELECT * FROM table1, table2 WHERE table1.id=table2.table1.id AND table2.name IN ('value1','value2')

HTH
Bubble

kemu_

using OR doesnt help either, as then it is enough if u have either of the two names in table2. But the thing is that i would need it to have two lines in table2. Not just either one of them but both... 🙁

nemonoman

You really should try to explain your problem in some detail. Rather than showing us examples of stuff that doesn't work, just say in simple words what you're trying to achieve.

Also, how wedded are you to your current database structure? Can you change it to get results more in line with the business problem?

Also, are you willing to take results from the DB and integrate them with PHP if that's your only option? It sounds at first take that you are requiring more intelligence in your soluton than can be compressed into a single SQL statement of reasonable complexity.

kemu_

heh, i suppose ur right nemonoman. My descriptions havent been excatly clear 😉 The thing what i want to arcieve is:

There r some objects in table1. Those objects can multiple parts in them (table2). So i need to know all those objects that have certain parts in them. Not just one part, but two or more.

And basically im not "stuck" with my db configuration, but id like to keep it this way. And it is also possible to do some of the work in PHP, but i was just asking if it could be done more easily with one sql-statement.

mrhappiness

SELECT t1.name
FROM
  table1 t1
  INNER JOIN table2 t2 ON t1.id = t2.table1_id AND t2.name = 'name1'
  INNER JOIN table2 dummy ON t1.tid = dummy.table1_id AND dummy.name = 'name2'

try this