Reading table with composite key, but only have first field of the key

Other Tech Topics
1

In Postgresql..

I have a table with a composite key, a1, and a2. I want to read all the records that have just the first field regardless of what is in the second part of the key.

I tried a1 = "xxx" but I just get one record (and I know there are three of them.)

Do I put NULL in the second part of the key?

Thanks!

2

This is a SQL Server forum. But there must be something else going on. Post your query

3

SELECT id_number, id_note FROM bridge_note WHERE id_number = " + 1st_part.

The table bridge_note has composite key of both id_number and id_note. I need ALL the records that have id_number as the first part of the key (say 1st_part = integer number 14.)

I could use a >= but then any records that have id_number > 14 would also be selected.

Thanks.

4 
WHERE id_number LIKE  '14%'

perhaps? So, fleshed out a bit:

SELECT id_number, id_note FROM bridge_note WHERE id_number = 1st_part + '%'
5

I figured out what the problem is.

First the SQL statement I used was just fine. Partial keys with = will find all the records with THAT part of the key.

The problem was when using PgSql connections IF you have one instance of the connection and try to read one table, and then another table with the SAME connection it will close the first instance of it when it tries to read the second table (and third if you have to go to another table.)

What you do is have more than one instance and yep, the first one stays open, and the second one reads the other table and,in my case, deletes the records in the second table before going back to read a second record in the first table.

What I'm doing is basically a cascading delete. Table A connects to bridge table B and bridge B connects to table C.
When deleting one record in Table A you have to find all the bridge records (B) and all the records in the third table (C) that connect back by the bridge. As you pickup a record in C you delete it. Then back to B and delete that record and then down one more in the table till you run out of related records. Then in the end delete the A record.

Our DBA is going to fix that one day so the SQL creation has a cascading delete but until they do, I have a way to cascade delete the related records in three tables for the online form I am working on.

Thanks for your help!

6

Glad you got it fixed :innocent:

You might want to find a support forum for PostgresSQL (for next time :smile: ) as this is a Microsoft SQL forum and folk here might not know much about PostgresSQL.

We have one client with one database that we pull data from, but apart from figuring out how to run that one query on PostgresSQL I, myself, have no further knowledge.

Although ti struck me as a much more capable database than MySQL, at the time (early 2000's), and I scratched my head as to why MySQL seemed to be way more popular.