select... 1

[plsremoveme]

Hey yall.
I've got a table which looks like this:

ID1 ID2
1 1
1 2
1 3
2 1
2 3
3 1
4 4

I want to have all those ID2's which have ID1=1 and ID1=2.
Desired result would be:
ID2: 1 and 3.

It might look kinda weird but i hope that you get what i want.
Thanks in advance!!!

Oliver

 

[Cagliostro]

select * from YourTable where ID1 in(1, 3) John Fill

ivfmd@mail.md

 

[plsremoveme]

Hmmm... wouldn't that be the same as this:

SELECT * FROM MyTable WHERE ID1 = '1' OR ID1 = '3'
?

Because both (your version and mine) do not give me the desired result :-(

But maybe i got you somehow wrong. Untill now I never heared of that "in" command.

TIA!

Oliver

 

[tlbroadbent]

John,

It is a little more complex than that. Here is one solution.

Select a.ID2
From tbl a Join tbl b
On a.ID2=b.ID2
Where a.ID1=1
And b.ID1=2 Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[carp]

A couple of approaches:

SELECT t1.ID1, t1.id2
FROM mytable t1, mytable t2
WHERE t1.id2 = t2.id2
AND t1.id1=1
AND t2.id1 = 2;

OR

SELECT id2
FROM myTable
WHERE id1 = 1
INTERSECT
SELECT id2
FROM myTable
WHERE id1=2;

 

[plsremoveme]

Hello everybody!

This approach:
SELECT id2
FROM myTable
WHERE id1 = 1
INTERSECT
SELECT id2
FROM myTable
WHERE id1=2;

caused an error. Maybe I should say that I use MySQL ? I am kinda new to this SQL-stuff...

Besides this: I only have ONE table with those two fields ID1 and ID2.

TIA. If you need any more information then just ask me!

Bye,
Oliver

 

[carp]

OK, the INTERSECT approach worked in Oracle. What are YOU using? It might have a counterpart to Oracle's INTERSECT.

The two solutions tlbroadbent and I posted all assume you only have one table.

 

[plsremoveme]

Hi!
Well I use MySQL. The result of the whole "SELECT" thingy will be used in PHP.
You say that the suggested solutions refer to ONE table. Then I must have misunderstood them. As i already said I am quite new to SQL, so maybe you could try to explain it to me once again.
I hope there is a possibilty to make this work with MySQL.

TIA!!!

Oliver

 

[plsremoveme]

Somehow my last answer was not sent properly.

Anyways i use "MySQL" and the result of this SELECT will be used in "PHP".
I am pretty new to SQL and I guess that is the reason for my probs.
I thought those other two suggestions would use two tables instead of one. I guess I don't understand them.

I hope there is a way to solve my problem in MySQL.

TIA!!!

Oliver

 

[tlbroadbent]

I just paid a quick visit to the MySQL web site (

http://www.mysql.com)

and looked at the online documentation. According to the docs, the query I proposed should work as should the first query proposed by carp.

As carp mentioned, both queries reference one table but the table is referenced twice. What we are doing is a self-join. Because the table is referenced twice, we use aliases in order to differentiate the two occurrences of one table in the query. In carp's query the aliases are t1 and t2. In mine the aliases are a and b.

Functionally, the queries are equivalent. The syntactical differences are due to my preference for the ANSI standard JOIN syntax.

I hope this explanation helps. Let us know if you have more questions. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[plsremoveme]

Alright...
...now i understand HOW it works. and finally i got the first example shown by carp run successfully. if i use yours which is quite similar but also different to that one i receive an error "1064"?
I'd like to know what is wrong with it?! For a better understanding of the whole SQL syntax and so on, maybe you could suggest a good book?
Meanwhile I will have a look at the MySQL onlince documentation.

So long and thanks for the great help. I was positively surprised by the really good feedback. Thanks!!!

Oliver

 

[tlbroadbent]

For starters, try two online SQL tutorials.

SQL Course 1:

http://www.sqlcourse.com/

SQL Course 2:

http://sqlcourse2.com/

I haven't read or used any ANSI SQL or MySQL books. My emphasis is MS SQL Server. However, I noted that one of the highly rated books on Amazon.com is "PHP and MySQL Web Development" by Luke Welling, Laura Thomson. Might be just what you need.
Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[plsremoveme]

Hey it's me again...
as I already said, the only version which works is the first one posted by carp.
Now I've got another problem...

How can I easily extend the select thingy?

Let's say now I want all the ID2's which have ID1=1 ID1=2 ID1=3 in common? or in other words ID1=1 ... ID1=n ?

I cannot figure out how to do this. Maybe you can help me.

TIA!!!

Oliver

 

[tlbroadbent]

Starting with carp's query...

SELECT t1.ID1, t1.id2
FROM mytable t1, mytable t2, mytable t3, ..., mytable tn
WHERE t1.id2 = t2.id2
AND t1.id2 = t3.ID2
AND t1.id2 = t4.ID2
.
.
.
AND t1.id2 = tn.ID2
AND t1.id1=1
AND t2.id1=2
AND t3.id1=3
AND t4.id1=4
.
.
.
AND tn.id1=n;

You may run into MySQL limitations on the number of tables you can join in a query. Another option is the following query.

SELECT ID1, id2
FROM mytable
WHERE id1=1
AND id2 IN (Select id2 From mytable where id1=2)
AND id2 IN (Select id2 From mytable where id1=3)
AND id2 IN (Select id2 From mytable where id1=4)
.
.
.
AND id2 IN (Select id2 From mytable where id1=n) Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[tlbroadbent]

I thought about this problem for a while today and came up with another solution. This one is interesting because it requires no JOINS and no SUB QUERIES.

Suppose I want to identify all ID2's which have ID1 = 1, 3 and 5.

Select ID2
From mytable
Where ID1 In (1,3,5)
Group By ID2
Having Sum(id1)=9

NOTE: The sum(ID1) = sum of the IN clause values.

Suppose I want to identify all ID2's which have ID1 = 1, 2, 3, 4, 5, 6 and 7.

Select ID2
From mytable
Where ID1 In (1,2,3,4,5,6,7)
Group By ID2
Having Sum(id1)=28 Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[plsremoveme]

Hello!
The one example with the sub selects does not work. I suppose it's because MySQL does not support any sub selects.
But the other two examples work really great!

I think I will use the last example you posted. It is the easiest one to extend.
I will have to look into this "IN" command and "GROUP BY" command to really understand what is going on in the whole query.
Thanks a lot!!!

Oliver

 

[carp]

I really like this last solution, but it does have a couple of restrictions:
1. You must be certain that each combination of id1 and id2 are unique. If your data looked like
ID1 ID2
3 3
3 3
3 3

then

Select ID2
From mytable
Where ID1 In (1,3,5)
Group By ID2
Having Sum(id1)=9

would return the value "3", even though this doesn't fit the requirements you are really looking for.

2. It won't work if you want to use the same approach with character data.

With that said, I must congratulate you for coming up with something a little more creative than a "vanilla" solution. This is the kind of thing that makes SQL so much fun!

 

[tlbroadbent]

carp,

Very good point about the restrictions. We often have solutions that work well within a well-defined set of parameters but fail miserably under other conditions. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[carp]

And I've created a BUNCH of them - only to have the restrictions painfully identified by hapless users!:-}