mySQL selecting from one table with conditions from another

Tischbein · 10-22-2007, 05:56 PM

Dear All,

I have two mySQL tables. One, yin, has a column called id while the other, yang, has a column called yin_id which is used to cross reference to the first table. Is there any way I can do listings based on both tables? E.g. in pseudocode:

select * from table where (select * from yang where yin_id=id)!= null;

That is, print those yin table rows that are cross referenced from yang. Thus, if yin is:

Code:

  id  name
   0  nopod
   1  monopod
   2  bipod
   3  tripod

(erm.. that was a C programmers count from 1-4, not my fault.)

and yang is:

Code:

  id  pid         yin_id
  1  canon.3240   2
  2  canon.1960   1

I'd like to get the yin rows mentioned in yang:

Code:

  id  name
   1  monopod
   2  bipod

Here's another that I'd like to do: Sort one table based in part on values given in the other, e.g.:

select * from yin sort by yin.prior + (yang_entry_pointing_to_yin_entry_exists? yang_entry.score : 3 );

I think I'll have to order a book, but if you can give me a headstart whilst I wait for it to arrive it would be much appreciated. Can you imagine how many caffeine induced speed-thumb twiddles I can get through in two days? My doc will never believe me when I tell him what caused my RSI. Books. I'd better browse a library first to find one that I like. Any recommendations?

Regards, Tischbein.

I could do Japanese hand dancing for variety.

Tischbein · 10-22-2007, 06:26 PM

My bad. I searched these forums first but not hard enough. On the upside LQ's system for detecting similar posts is really good!

To select yin rows referenced in yang:

Code:

select * from yin left join yang on (yin.id=yang.yin_id) where yang.id > 45;

(I even get to throw in a where clause!)

Haven't found a solution to the second question yet though.

Regards, Tischbein.

PAix · 10-22-2007, 07:17 PM

Hi Tischbein,

I think I can help you with the first part. Where I use aliases a and b which are assigned in the FROM clause, you can simply use tablename.fieldname (if you enjoy typing and eyestrain).
I assume that you agree that all table fields should be declared as "not null" as there is a lot of grief to be gained from allowing null data in tables. Testing for null or not being equal to null in result tables has validity. Selecting a.* below would return all fields in the yin table and b.* display all fields from the yang table [ select yin.*, yang.* ]. A plain select * from multiple tables will succeed only if the fieldnames are unique between the two tables.
Selecting * is not good practice as the order of the fields is often as declared in the table definition, but you can catch a crab if someone changes a table definition by adding another field and occasionally an upgrade to a new dbms version can false foot you too. If you are explicit with what fields you wish to display, you will at least ignore an added field or get an error if the fieldname is changed; perhaps someone spelled it incorrectly and decided to clean it up before you went to final test and production! The display field names can be changed like this:

Code:

SELECT a.id AS myyinid, a.name AS forename

This is what you want though -

Code:

SELECT a.id, a.name
FROM yin.a, yang b
WHERE a.id = b.yin_id

If you must use SELECT * , then you can I believe refer to col1, col2, col3 etc in the output, as long as some joker hasn't false footed you with a change somewhere.
This is all untested but believed to be good; my experience was mainly as an Ingres dbms application developer.

Just noticed your second post and can't figure where the > 45 came into the equation?

Not entirely sure I understand your requirement in the second part of your question. Good luck.
No real recommendation for a book, but you could either try the MySQL documentation or have a look at the documentation downloads for Open Ingres. There are a lot of pdf manuals that can be downloaded and the SQL ones while not being MySQL are pretty much ansi SQL. A good starting point at no cost and a rich source of examples. I appreciate that MySQL will do a lot more, but time enough for the power stuff when you have a firm grip on the SQL for free

Good hunting, keep digging at the code-face.
PAix

jlinkels · 10-22-2007, 08:17 PM

You can order on anything you want. You can do the "left join" more often if you need to make other combinations of yin and yang. But I think your actual problem is more complicated than this. I don't see any other way to sort on but on the two values in yin and the two values in yang and that cannot vbe that hard.

Anyway, if you want to sort on yang.yin_id for those values which do occur in yin:

Code:

select * from yin left join yang on (yin.id=yang.yin_id) where yang.id > 45 ORDER BY yang.yin_id;

But imagine you had another column in yang, priority, you could do:

Code:

select * from yin left join yang on (yin.id=yang.yin_id) where yang.id > 45 ORDER BY yang.priority

Since you already joined on yin.id=yang.yin_id only values of yang.yin_id will show up which have an existing value for yin.id.

BTW it is good practice to write MySQL keywords in CAPITALS. When your queries get a little bit more complicated (like some of mine, which are over 600 characters) it is a bit easier to visually splt a string.

jlinkels

spirit receiver · 10-23-2007, 05:15 AM

I understand you want to calculate a new sort key from data in both tables, and the calculation method depends on the actual data? You can use a subquery for that, as in the following example:

Code:

SELECT id, 
       name 
FROM ( SELECT yin.*, 
              yang.id AS yang_id
       FROM yin INNER JOIN yang on yin.id=yang.id 
       UNION 
       SELECT yin.*,
              3
       FROM yin LEFT JOIN yang on yin.id=yang.id 
       WHERE yang.id IS NULL ) AS temp 
ORDER BY ( id + yang_id );

The result in your case will be

Code:

+----+---------+
| id | name    |
+----+---------+
|  1 | monopod |
|  0 | nopod   |
|  2 | bipod   |
|  3 | tripod  |
+----+---------+
4 rows in set (0.00 sec)

This table is taken from the result of the subquery "(SELECT ... UNION ... SELECT ...)" where I used UNION for the case differentiation. The subquery itself gives:

Code:

+----+---------+---------+
| id | name    | yang_id |
+----+---------+---------+
|  1 | monopod |       1 |
|  2 | bipod   |       2 |
|  0 | nopod   |       3 |
|  3 | tripod  |       3 |
+----+---------+---------+
4 rows in set (0.00 sec)

Tischbein · 10-23-2007, 07:19 PM

Hahha, I like that one, digging at the code-face. Besmirched and grubby like the boy I would be if my landlady wouldn't turf me out for it! Me lad ye haven't eaten, ye haven't done any laundry of late and there are apples to pick when you have a spare moment. Oh, she could be mother to me!

Thank you all for your answers, especially spirit_receiver, that answers my question very nicely!

You may have wondered why I'm trying to make life complicated by having two corresponding tables but with missing entries in one. The answer is that I'm hoping to end up with a huge yin table and lots of small, disposable yang tables. (yang == bad spirit is always disposable, right?) I'm curious to see how it scales, especially when I'm plucking out the top few entries of a weighted random ordering. If mysql manages to cope with returning the top entry from sorting by RAND() + yin.prior + (yang.score == null? 3 : yang.score) at speed I shall be most impressed. That means that MYsql spots the RAND() and deduces that it doesn't actually have to sort and can instead apply a fast randomised algorithm. That counts as very impressive in my book. If not I wouldn't blame it. I'll have to delve deeper if I have to implement the random choices myself. But we'll see. PAix's documentation will help show me the way.

Regards, Tischbein.