Read data from database using named queries

Hello Friends,

I’m trying to read data from the database using named queries.

SELECT
users.name,
sample.details,
sample.date
FROM sample, users
WHERE (number = :Number and date IS NULL)

I’m used the above query to read two table data from the database using named queries. I’m trying to display data only the date IS NULL for the selected component number. but not getting the proper solution. My error is the display of all user name every time.

My requirement is the display the only date IS NULL and also who it is user for this date. only this user display. In my case every time displays all users.

name details date
A xyz NULL
B xyz NULL
C xyz NULL
D xyz NULL

Above output display, this is wrong output. my requirement is like below
name details date
A xyz NULL

Please can you give me any suggestions?

Thanks,
Priyanka

@victordcq please can you suggest any idea?

you will need to use a join with users then i suppose.
i will need to see your database structure to help out more

instead of
FROM sample, users
you will need something like
FROM sample INNER JOIN users ON users.id=sample.user
where users.id=sample.user should be how both tables are linked, but for that i need to know your db

1 Like

Using a comma is old-style and will perform an INNER JOIN. However without an ON clause, it will join every row from each table together which is probably not what you want.. (e.g. for every row in sample it will join every row in users together. If sample has 5 rows and users has 5 rows, then the result would have 5x5=25 rows)

1 Like

@victordcq and @nminchin Thank you so much for your help.

1 Like

@victordcq Please can you suggest how to also include a timestamp for the report? the timestamp is not in the database but my requirement is the also included timestamp.

you would have to add a timestamp to the database then.
what database do you use?
i dont know how reports work tho

CROSS JOIN not INNER JOIN.

1 Like

That’s what I thought, but I saw this on stack overflow

It certainly behaves like a cross join though… I’ve never tried an inner join without an on condition though, so maybe it’s the same?

The comma is definitely a cross join (or Cartesian product – same thing). Pretty easy to mock up a couple of small tables and see.

I’ve seen this claim that it’s an inner join before and I think it comes from a misunderstanding of what the query compiler/optimizer is doing behind the scenes. If there’s a cross join and a corresponding where clause that gives you a result like an inner join, the optimizer changes it to a more efficient inner join for you. And if you look at it with the explain command, you see it being processed as an inner join. But that’s all dependent on the correct where clause existing.

4 Likes